﻿Imports System.Data.SqlClient

Public Class frmClaimsReports

    Dim MinDate, MaxDate As String

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        MinDate = "N'" & Me.DTPPeriodFRm.Value.ToString("dd/MM/yyyy") & "' "
        MaxDate = "N'" & Me.DTPPeriodTo.Value.ToString("dd/MM/yyyy") & "' "

        If Me.RClientStatement.Checked = True Then
            If Me.txtClientName.Text.Trim.Length = 0 Then
                MsgBox("Please select a valid client")
                Me.txtClientID.Focus()
            Else
                ShowClientStatement()
            End If

        ElseIf Me.RClaimsClientsAnalysis.Checked = True Then
            ShowClaimsClientsAnalysisPayments()

        ElseIf Me.RClaimsInsuranceCompanyNoOFClaims.Checked = True Then
            ShowClaimsInsuranceCompanyNoOFClaims()

        ElseIf Me.RClaimsInsuranceCompanyAnalysis.Checked = True Then
            ShowClaimsInsuranceCompanyPayments()

        ElseIf Me.RClaimsAnalysisMonthly.Checked = True Then
            ShowClaimsAnalysisMonthly()

        ElseIf Me.RClaimsRejectedAnalysis.Checked = True Then
            ShowClaimsRejectedAnalysis()

        End If
    End Sub

    Sub ShowClientStatement()
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim dap As New SqlDataAdapter("Select ClaimType,ClaimNo,Status,ClientID,ClientName,InsuranceCompany,PolicyNo," & _
                                          "MembName + ' (' + MedIDCarNo + ')' as MedIDCarNo,DateOfReceive,ClaimAmount," & _
                                          "dbo.GetClaimPaidAmount(SNo) AmountPaid, dbo.GetClaimRejectedAmount(SNo) AmountRejected " & _
                                          "From Claims where ClientID=" & CInt(Me.txtClientID.Text.Trim) & " and " & _
                                          "TransDate >= N'" & Me.DTPPeriodFRm.Value.ToShortDateString & " 00:00:00' " & _
                                          "and TransDate <= N'" & Me.DTPPeriodTo.Value.ToShortDateString & " 23:59:59'", cnn)

            Dim das As New DataSet

            dap.Fill(das, "Claims")

            Dim rpt As New ClaimsClientStatement
            rpt.SetDataSource(das)
            rptViewer.CrystalReportViewer1.ReportSource = rpt
            rptViewer.CrystalReportViewer1.RefreshReport()
            rptViewer.ShowDialog()

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Sub ShowClaimsInsuranceCompanyNoOFClaims()
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim dap As New SqlDataAdapter("Select * From Claims where " & _
                                          "TransDate >= N'" & Me.DTPPeriodFRm.Value.ToShortDateString & " 00:00:00' " & _
                                          "and TransDate <= N'" & Me.DTPPeriodTo.Value.ToShortDateString & " 23:59:59'", cnn)

            Dim das As New DataSet

            dap.Fill(das, "Claims")

            Dim rpt As New ClaimsInsuranceCompanyAnalysisNoOfClaims
            rpt.SetDataSource(das)
            rptViewer.CrystalReportViewer1.ReportSource = rpt
            rptViewer.CrystalReportViewer1.RefreshReport()
            rptViewer.ShowDialog()

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Sub ShowClaimsInsuranceCompanyPayments()
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim dap As New SqlDataAdapter("Select " & MinDate & " DateOfReceive," & MaxDate & " DateOfLoss,InsuranceCompany," & _
                                          "Count(*) SNo,Sum(ClaimAmount) ClaimAmount,Sum(dbo.GetClaimPaidAmount(SNo)) AmountPaid, " & _
                                          "Sum(dbo.GetClaimRejectedAmount(SNo)) AmountRejected From Claims where " & _
                                          "TransDate >= N'" & Me.DTPPeriodFRm.Value.ToShortDateString & " 00:00:00' " & _
                                          "and TransDate <= N'" & Me.DTPPeriodTo.Value.ToShortDateString & " 23:59:59' " & _
                                          "Group By InsuranceCompany", cnn)

            Dim das As New DataSet

            dap.Fill(das, "Claims")

            Dim rpt As New ClaimsInsuranceCompanyAnalysisPayments
            rpt.SetDataSource(das)
            rptViewer.CrystalReportViewer1.ReportSource = rpt
            rptViewer.CrystalReportViewer1.RefreshReport()
            rptViewer.ShowDialog()

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Sub ShowClaimsAnalysisMonthly()
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim dap As New SqlDataAdapter("Select " & MinDate & " DateOfReceive," & MaxDate & " DateOfLoss,Month(TransDate) CNo," & _
                                          "Count(*) SNo,Sum(ClaimAmount) ClaimAmount,Sum(dbo.GetClaimPaidAmount(SNo)) AmountPaid, " & _
                                          "Sum(dbo.GetClaimRejectedAmount(SNo)) AmountRejected From Claims " & _
                                          "where Year(TransDate)=" & Me.DTPPeriodFRm.Value.Year & "Group By Month(TransDate)", cnn)
            Dim das As New DataSet

            dap.Fill(das, "Claims")

            Dim rpt As New ClaimsAnalysisMonthly
            rpt.SetDataSource(das)
            rptViewer.CrystalReportViewer1.ReportSource = rpt
            rptViewer.CrystalReportViewer1.RefreshReport()
            rptViewer.ShowDialog()

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Sub ShowClaimsClientsAnalysisPayments()
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim dap As New SqlDataAdapter("Select " & MinDate & " DateOfReceive," & MaxDate & " DateOfLoss,ClientName," & _
                                          "Count(*) SNo,Sum(ClaimAmount) ClaimAmount,Sum(dbo.GetClaimPaidAmount(SNo)) AmountPaid, " & _
                                          "Sum(dbo.GetClaimRejectedAmount(SNo)) AmountRejected From Claims where " & _
                                          "TransDate >= N'" & Me.DTPPeriodFRm.Value.ToShortDateString & " 00:00:00' " & _
                                          "and TransDate <= N'" & Me.DTPPeriodTo.Value.ToShortDateString & " 23:59:59' " & _
                                          "Group By ClientName", cnn)

            Dim das As New DataSet

            dap.Fill(das, "Claims")

            Dim rpt As New ClientsAnalysisPayments
            rpt.SetDataSource(das)
            rptViewer.CrystalReportViewer1.ReportSource = rpt
            rptViewer.CrystalReportViewer1.RefreshReport()
            rptViewer.ShowDialog()

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Sub ShowClaimsRejectedAnalysis()
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim dap As New SqlDataAdapter("Select " & MinDate & " SavedDate," & MaxDate & " SavedUser,dbo.GetClaimInsurComp (ClaimNo) ClientName," & _
                                          "Amount,RejectionReason From ClaimsRejections where " & _
                                          "SavedDate >= N'" & Me.DTPPeriodFRm.Value.ToShortDateString & " 00:00:00' " & _
                                          "and SavedDate <= N'" & Me.DTPPeriodTo.Value.ToShortDateString & " 23:59:59'", cnn)

            Dim das As New DataSet

            dap.Fill(das, "ClaimsRejections")

            Dim rpt As New ClaimsRejectedAnalysis
            rpt.SetDataSource(das)
            rptViewer.CrystalReportViewer1.ReportSource = rpt
            rptViewer.CrystalReportViewer1.RefreshReport()
            rptViewer.ShowDialog()

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
        Me.Close()
    End Sub

    Private Sub Button3_Click(sender As System.Object, e As System.EventArgs) Handles Button3.Click
        Dim a As New frmSearchCustID
        a.ShowDialog()

        If SelClientID = 0 Then
            Exit Sub
        End If

        Me.txtClientID.Text = SelClientID
        Me.txtClientName.Text = SelClientName
    End Sub

    Private Sub txtClientNo_KeyUp(sender As Object, e As System.Windows.Forms.KeyEventArgs) Handles txtClientID.KeyUp
        If Me.txtClientID.Text.Trim.Length > 0 Then
            If e.KeyCode = Keys.Enter Then
                Me.txtClientName.Text = GetClientName(CInt(Me.txtClientID.Text))
            End If
        End If
    End Sub

    Private Sub txtClientNo_TextChanged(sender As System.Object, e As System.EventArgs) Handles txtClientID.TextChanged
        Me.txtClientName.Clear()
    End Sub
End Class
